use v6.d;
use Grammar::Debugger;
use DB::SQLite;
use DDL;
use DSN;
use DB::Connection;
use DB::Result;


role Mold {

    has $.dsn;
	has $.ddl;
    
    my $connection;
    
	method connection() {
	    return $connection;
	}

	method connect($source -->DB::Connection) {
        #say $source;
        #say DSN.parse($source);
		$connection =  DB::SQLite.new(filename => DSN.parse($source)<host>.Str).db;
		return $connection;
	}

	method table-name(-->Str) {
        my $table-name = DDL.parse($!ddl).<table-name>.Str || '';
	    return $table-name;
	}

	method insert(%field-data) {
        my $db = self.connection or $db = self.connect($!dsn);
		my $sql-insert = 'INSERT INTO ' ~ self.table-name ~ ' (' ~ %field-data.keys.join(', ') ~ ' ) VALUES (' ~ %field-data.values.map: {'?'}==>join(', ')  ~ ')';
		my @values = %field-data.values;
		return $db.query($sql-insert,|@values);
	}

	method col-names() {
        return DDL.parse($!ddl).<column-section><column>.>><column-name>.>>.Str
    }

	method delete(%filter) {
		my $db = self.connection or $db = self.connect($!dsn);
		my $sql-del = 'DELETE FROM ' ~ self.table-name ~ ' WHERE ' ~ %filter.pairs.map:{$_.key ~ ' = ?'  }==>join(',');
		
        my @values = values %filter;
		return $db.query($sql-del, |@values);
    }

    # method update(%values, %filter) {...}

	# method count(%filter) {...}

	method select(@cols, %filter) {
		my $db = self.connection or $db = self.connect($!dsn);
		my $sql-select = 'SELECT ' ~ @cols.join(', ') ~ ' FROM ' ~ 
            self.table-name ~ ' WHERE ' ~ %filter.pairs.map(*.key ~ ' = ? ').join(' AND ');
		my @values = %filter.values;
		return $db.query($sql-select,|@values);
	}
}


class Test-Up does Mold {

    

   submethod BUILD {
       $!ddl = q:to/STR/;
CREATE TABLE Info2 (
    aab001 VARCHAR(12) NOT NULL,
    单位名称 VARCHAR(60),
    单位编号 VARCHAR(14),
    dwbm VARCHAR(5),
    dwmc VARCHAR(60),
    scbh VARCHAR(11),
    个人编号 VARCHAR(14) NOT NULL PRIMARY KEY,
    姓名 VARCHAR(60) NOT NULL,
    身份证号 VARCHAR(18) NOT NULL,
    性别 VARCHAR(6),
    民族 VARCHAR(60),
    出生日期 VARCHAR(12),
    工作日期 VARCHAR(12),
    退休日期 VARCHAR(12),
    参保状态 VARCHAR(10),
    缴费状态 VARCHAR(10),
    首次参保 VARCHAR(12),
    本次参保 VARCHAR(12),
    缴费基数 DECIMAL(12,2),
    养老金  DECIMAL(12,2),
    发放账号 VARCHAR(30),
    发放机构 VARCHAR(60),
    TQBZ VARCHAR(10),
    MDDW VARCHAR(10),
    LX2013 VARCHAR(10),
    备注 VARCHAR(200)
)
STR
    $!dsn = 'sqlite://@mydata.sqlite3'	
}
}
sub MAIN() {


    my $t = Test-Up.new;

   say $t.select(['姓名','身份证号','出生日期','缴费基数','单位名称'],{'姓名'=>'李明'}).hashes;

}
